package web.util;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.FileOutputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by Administrator on 2016/8/4.
 */
public class DownloadUtil {

    /** 简单excel，只有首页
     * @param head 表头集合
     * @param list 内容集合
     * @param paths 保存路径
     * @param sheetName 首页sheet名称
     */
    public static boolean getExcel(String[] head,List<Map<String,String>> list,String paths,String sheetName){
        int rowNum=0;
        // 第一步，创建一个webbook，对应一个Excel文件
        HSSFWorkbook wb = new HSSFWorkbook();
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);
        sheet.setActive(true);//取消受保护的视图
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short

        //==================================================样式====================================================
        /** 样式：粗体，居中 */
        HSSFFont fontHead = wb.createFont();
        fontHead.setFontName("仿宋_GB2312");
        fontHead.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
        fontHead.setFontHeightInPoints((short) 13);
        HSSFCellStyle styleHead = wb.createCellStyle();
        styleHead.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        styleHead.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中
        styleHead.setFont(fontHead);
        /**样式： 上下左右居中 */
        HSSFCellStyle styleHead_1 = wb.createCellStyle();
        styleHead_1.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        styleHead_1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中
        /**样式：链接  */
        HSSFCellStyle style_a=wb.createCellStyle();
        HSSFFont font_a = wb.createFont();
        font_a.setColor(HSSFColor.BLUE.index);
        style_a.setFont(font_a);
        /** 上下居中*/
        HSSFCellStyle styleHead_up = wb.createCellStyle();
        styleHead_up.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中
        /** 自动换行 */
        HSSFCellStyle style_auto=wb.createCellStyle();
        style_auto.setWrapText(true);


        //================================================== end 样式 ====================================================

        /** 首行合并显示标题  */
        //CellRangeAddress  对象的构造方法需要传入合并单元格的首行、最后一行、首列、最后一列。
              /*CellRangeAddress cra=new CellRangeAddress(0, 0, 0, head.size());
              //合并单元格所使用的方法：
              sheet.addMergedRegion(cra);*/

        /** 创建标题行 */
        HSSFRow headRow=sheet.createRow(rowNum++);
        headRow.setHeight((short)(200*2));
        for (int i = 0; i < head.length; i++) {
            HSSFCell c = headRow.createCell(i);
            c.setCellValue(head[i]);
            c.setCellType(Cell.CELL_TYPE_STRING);//设置数据类型0：int，1String
            c.setCellStyle(styleHead); //样式
        }

        /** 内容区域*/
        for(Map<String,String> m:list){
            int i=0;
            HSSFRow contRow=sheet.createRow(rowNum++);
            //发布时间
            HSSFCell releaseTime=contRow.createCell(i++);
            releaseTime.setCellValue(m.get("releaseTime"));
            releaseTime.setCellType(HSSFCell.CELL_TYPE_STRING);
            releaseTime.setCellStyle(styleHead_1);
            //地区
            HSSFCell location=contRow.createCell(i++);
            location.setCellValue(m.get("location"));
            location.setCellType(HSSFCell.CELL_TYPE_STRING);
            location.setCellStyle(styleHead_1);
            //类型
            HSSFCell type=contRow.createCell(i++);
            type.setCellValue("");
            type.setCellType(HSSFCell.CELL_TYPE_STRING);
            type.setCellStyle(styleHead_1);
            //标题
            HSSFCell titleText=contRow.createCell(i++);
            String str="HYPERLINK(\"#'"+m.get("id")+"'!A1\",\""+m.get("titleText")+"\")";
            titleText.setCellFormula(str);
            titleText.setCellStyle(style_a);
            //金额
            HSSFCell item_amount=contRow.createCell(i++);
            item_amount.setCellStyle(styleHead_1);
            if("0.0".equals(m.get("item_amount"))){
                item_amount.setCellValue("");
                item_amount.setCellType(HSSFCell.CELL_TYPE_STRING);
            }else{
                item_amount.setCellValue(m.get("item_amount"));
                item_amount.setCellType(HSSFCell.CELL_TYPE_STRING);
            }
            //联系人
            HSSFCell c5=contRow.createCell(i++);
            c5.setCellValue(m.get("subscriber_contact"));
            c5.setCellType(HSSFCell.CELL_TYPE_STRING);
            //联系方式
            HSSFCell c6=contRow.createCell(i++);
            c6.setCellValue(m.get("subscriber_phone"));
            c6.setCellType(HSSFCell.CELL_TYPE_STRING);
            //招标编号
            HSSFCell c7=contRow.createCell(i++);
            c7.setCellValue(m.get("tender_id"));
            c7.setCellType(HSSFCell.CELL_TYPE_STRING);
            //招标单位
            HSSFCell c8=contRow.createCell(i++);
            c8.setCellValue(m.get("subscriber"));
            c8.setCellType(HSSFCell.CELL_TYPE_STRING);
            //代理机构
            HSSFCell c9=contRow.createCell(i++);
            c9.setCellValue(m.get("agent"));
            c9.setCellType(HSSFCell.CELL_TYPE_STRING);
            //截止时间
            HSSFCell c10=contRow.createCell(i++);
            c10.setCellValue(m.get("endTime"));
            c10.setCellType(HSSFCell.CELL_TYPE_STRING);
            c10.setCellStyle(styleHead_1);
        }

        /* 添加链接点击sheet */
        for(Map<String,String> m:list){
            HSSFSheet sheet99 = wb.createSheet(m.get("id"));
            sheet99.setActive(true);//取消受保护的视图
            int i=0;
            sheet99.createRow(i).createCell(0).setCellValue("发布时间");
            sheet99.getRow(i).createCell(1).setCellValue(m.get("releaseTime"));
            sheet99.getRow(i).createCell(2).setCellFormula("HYPERLINK(\"#'" + sheetName + "'!A1\",\"返回主界面\")");
            sheet99.getRow(i++).getCell(2).setCellStyle(style_a);

            sheet99.createRow(i).createCell(0).setCellValue("标题");
            sheet99.getRow(i).createCell(1).setCellValue(m.get("titleText"));
            sheet99.getRow(i++).createCell(2).setCellValue("");

            sheet99.createRow(i).createCell(0).setCellValue("类型");
            sheet99.getRow(i++).createCell(1).setCellValue(m.get("zbType"));

            sheet99.createRow(i).createCell(0).setCellValue("地区");
            sheet99.getRow(i++).createCell(1).setCellValue(m.get("location"));

            sheet99.createRow(i).createCell(0).setCellValue("招标单位");
            sheet99.getRow(i++).createCell(1).setCellValue(m.get("subscriber"));

            sheet99.createRow(i).createCell(0).setCellValue("代理机构");
            sheet99.getRow(i++).createCell(1).setCellValue(m.get("agent"));

            sheet99.createRow(i).createCell(0).setCellValue("招标编号");
            sheet99.getRow(i++).createCell(1).setCellValue(m.get("tender_id"));

            sheet99.createRow(i).createCell(0).setCellValue("网站链接");
            try {
                sheet99.getRow(i).createCell(1).setCellFormula("HYPERLINK(\"" + m.get("url") + "\",\"" + m.get("url") + "\")");
            } catch (Exception e) {
                System.out.println("异常链接："+m.get("url"));
                sheet99.getRow(i).createCell(1).setCellFormula("HYPERLINK(\"" + "" + "\",\"" + "" + "\")");
                e.printStackTrace();
            }
            sheet99.getRow(i).getCell(1).setCellStyle(style_a);
            sheet99.getRow(i++).createCell(2).setCellValue("");

            sheet99.createRow(i).createCell(0).setCellValue("项目正文");
            sheet99.getRow(i).getCell(0).setCellStyle(styleHead_up);
            sheet99.getRow(i).createCell(1).setCellValue(m.get("content").length()>10001?m.get("content").substring(0,10000):m.get("content"));
            sheet99.getRow(i).getCell(1).setCellStyle(style_auto);
            sheet99.getRow(i).createCell(2).setCellValue("");
            sheet99.getRow(i++).setHeight((short)(300*20));
            /** 设置宽度*/
            sheet99.setColumnWidth(0, 15 * 256);
            sheet99.setColumnWidth(1, 80 * 256);
            sheet99.setColumnWidth(2, 15 * 256);
        }


        //设置Excel的自动筛选
        CellRangeAddress c = CellRangeAddress.valueOf("A1:K1");
        sheet.setAutoFilter(c);
        /** 设置宽度*/
        sheet.setColumnWidth(0, 15 * 256);
        sheet.setColumnWidth(1, 15 * 256);
        sheet.setColumnWidth(2, 10 * 256);
        sheet.setColumnWidth(3, 100 * 256);
        sheet.setColumnWidth(4, 15 * 256);
        sheet.setColumnWidth(5, 15 * 256);
        sheet.setColumnWidth(6, 20 * 256);
        sheet.setColumnWidth(7, 30 * 256);
        sheet.setColumnWidth(8, 40 * 256);
        sheet.setColumnWidth(9, 40 * 256);
        sheet.setColumnWidth(10, 15 * 256);

        /** 保存*/
        try {
            FileOutputStream fout = new FileOutputStream(paths);
            wb.write(fout);
            fout.close();
            //Logger.getLogger(DownloadUtil.class).info("Excel导出完成：" +paths);
            return true;}
        catch (Exception e) {
            Logger.getLogger(DownloadUtil.class).error("Excel下载功能导出失败！");
            e.printStackTrace();
            return false;
        }

    }


    /**
     * 带链接跳转的excel
     * @param list  首页的展示信息
     * @param newsMap 通过招标单位,得到中标公司
     * @param mapSuccessContact 通过中标公司得到，中标联系方式
     * @param paths 保存路径
     * @return
     */
    public static boolean getExcelYJT(
            String key,List<Map<String,Object>> list,Map<String,List<String>> newsMap,Map<String,Map<String,Object>> mapSuccessContact,
            String paths){
        //第三层表格类容
        Map<String,List<Map<String,Object>>> s3_Map=new HashMap<String, List<Map<String, Object>>>();

        // 第一步，创建一个webbook，对应一个Excel文件
        HSSFWorkbook wb = new HSSFWorkbook();


        //==================================================样式====================================================
        //字体大小
        HSSFFont font_all = wb.createFont();
        font_all.setFontHeightInPoints((short) 11);
        /** 样式：粗体，居中 */
        HSSFFont fontHead = wb.createFont();
        fontHead.setFontName("仿宋_GB2312");
        fontHead.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
        fontHead.setFontHeightInPoints((short) 14);
        HSSFCellStyle styleHead = wb.createCellStyle();
        styleHead.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        styleHead.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中
        styleHead.setFont(fontHead);
        /**样式： 上下左右居中 */
        HSSFCellStyle styleHead_1 = wb.createCellStyle();
        styleHead_1.setFont(font_all);
        styleHead_1.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        styleHead_1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中
        /**样式： 上下居中 */
        HSSFCellStyle styleHead_2 = wb.createCellStyle();
        styleHead_2.setFont(font_all);
        styleHead_2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中
        /**样式：链接  */
        HSSFCellStyle style_a=wb.createCellStyle();
        HSSFFont font_a = wb.createFont();
        font_a.setColor(HSSFColor.BLUE.index);
        font_a.setFontHeightInPoints((short) 11);
        style_a.setFont(font_a);
        /*含左右居中的链接样式  */
        HSSFCellStyle style_a1=wb.createCellStyle();
        HSSFFont font_a1 = wb.createFont();
        font_a1.setColor(HSSFColor.BLUE.index);
        font_a1.setFontHeightInPoints((short) 11);
        style_a1.setFont(font_a1);
        style_a1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        /** 上下居中*/
        HSSFCellStyle styleHead_up = wb.createCellStyle();
        styleHead_up.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中
        /** 自动换行 */
        HSSFCellStyle style_auto=wb.createCellStyle();
        style_auto.setWrapText(true);
        //================================================== end 样式 ====================================================

        int rowNum=0;
        /* 标题行 */
        String[] head={"序号","标题","时间","招标单位","招标单位联系人","招标单位联系电话","操作"};
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet("主界面");
        sheet.setActive(true);//取消受保护的视图
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short

        /** 创建标题行 */
        HSSFRow headRow=sheet.createRow(rowNum++);
        headRow.setHeight((short)(200*2));
        for (int i = 0; i < head.length; i++) {
            HSSFCell c = headRow.createCell(i);
            c.setCellValue(head[i]);
            c.setCellType(Cell.CELL_TYPE_STRING);//设置数据类型0：int，1String
            c.setCellStyle(styleHead); //样式
        }

        /** 内容区域*/
        for(Map<String,Object> map:list){
            int i=0;
            HSSFRow contRow=sheet.createRow(rowNum++);
            //序号
            HSSFCell type=contRow.createCell(i++);
            type.setCellValue(rowNum-1);
            type.setCellType(HSSFCell.CELL_TYPE_STRING);
            type.setCellStyle(styleHead_1);
            //标题
            HSSFCell titleText=contRow.createCell(i++);
            String titleHref="http://zb.zbmao.cn/home/snapShot?keyList="+key+"&districtNewsId="
                    +map.get("district_news_id")+"&location="+ map.get("province_id");
            titleText.setCellFormula("HYPERLINK(\"" + titleHref + "\",\"" + StringOperateUtil.reString(map.get("title")+"") + "" + "\")");
            titleText.setCellStyle(style_a);
            //时间
            HSSFCell t1=contRow.createCell(i++);
            t1.setCellValue(map.get("starTime")+"");
            t1.setCellType(HSSFCell.CELL_TYPE_STRING);
            t1.setCellStyle(styleHead_1);
            //招标单位
            HSSFCell t2=contRow.createCell(i++);
            t2.setCellValue(map.get("subscriber")+"");
            t2.setCellType(HSSFCell.CELL_TYPE_STRING);
            t2.setCellStyle(styleHead_2);
            //招标单位联系人
            HSSFCell t3=contRow.createCell(i++);
            t3.setCellValue(map.get("subscriber_contact")+"");
            t3.setCellType(HSSFCell.CELL_TYPE_STRING);
            t3.setCellStyle(styleHead_1);
            //招标单位联系电话
            HSSFCell t4=contRow.createCell(i++);
            t4.setCellValue(map.get("subscriber_phone")+"");
            t4.setCellType(HSSFCell.CELL_TYPE_STRING);
            t4.setCellStyle(styleHead_1);
            //查看按钮，并创建链接页面
            HSSFCell t5=contRow.createCell(i++);
            String subs=map.get("subscriber")+"";
            List<String> zbGs=newsMap.get(subs);
            if(zbGs.size()==0){
                t5.setCellValue("未查到相关数据");
                t5.setCellType(HSSFCell.CELL_TYPE_STRING);
                t5.setCellStyle(styleHead_1);
            }else{
                String str="HYPERLINK(\"#'"+(rowNum+"_s2")+"'!A1\",\"查看\")";
                t5.setCellFormula(str);
                t5.setCellStyle(style_a1);

            /* 创建链接页面 */
            HSSFSheet sheet99 = wb.createSheet(rowNum+"_s2");
            sheet99.setActive(true);//取消受保护的视图
            int j1=0;
            HSSFRow contRow01=sheet99.createRow(j1++);
            contRow01.createCell(0).setCellValue("中标公司");
            contRow01.createCell(1).setCellValue("手机");
            contRow01.createCell(2).setCellValue("固话");
            contRow01.createCell(3).setCellValue("邮箱");
            contRow01.createCell(4).setCellFormula("HYPERLINK(\"#'"+"主界面"+"'!A1\",\"回到首页\")");
            contRow01.getCell(4).setCellStyle(style_a);
            contRow01.getCell(0).setCellStyle(styleHead);
            contRow01.getCell(1).setCellStyle(styleHead);
            contRow01.getCell(2).setCellStyle(styleHead);
            contRow01.getCell(3).setCellStyle(styleHead);
            for(int n=0;n<zbGs.size();n++)  {
                String s=zbGs.get(n);
                Map<String,Object> m99=mapSuccessContact.get(s);
                HSSFRow contRow99=sheet99.createRow(j1++);
                contRow99.createCell(0).setCellValue(s);
                contRow99.getCell(0).setCellStyle(styleHead_2);
                contRow99.createCell(1).setCellValue(m99.get("cellphone")==null?"":m99.get("cellphone")+"");
                contRow99.getCell(1).setCellStyle(styleHead_2);
                contRow99.createCell(2).setCellValue(m99.get("com_phone")==null?"":m99.get("com_phone")+"");
                contRow99.getCell(2).setCellStyle(styleHead_2);
                contRow99.createCell(3).setCellValue(m99.get("email")==null?"":m99.get("email")+"");
                contRow99.getCell(3).setCellStyle(styleHead_2);
                contRow99.createCell(4).setCellValue("");
                List<Map<String,Object>> s3_l=(List<Map<String,Object>>)m99.get("listNews");
                if(s3_l.size()!=0){
                    for(Map<String,Object> m:s3_l){
                        HSSFRow contRows3_m=sheet99.createRow(j1++);
                        String  title_href_3="http://zb.zbmao.cn/home/result_snapShot?keyList="+key+"&districtNewsId="+m.get("district_news_id")+"&location="+m.get("provinceId");
                        contRows3_m.createCell(0).setCellFormula("HYPERLINK(\"" + title_href_3 + "\",\"" + (m.get("title") == null ? "" : StringOperateUtil.reString(m.get("title") + "")) + "" + "\")");
                        contRows3_m.getCell(0).setCellStyle(style_a);
                    }
                }

                //空出一行，与前面分开
                HSSFRow contRow99_=sheet99.createRow(j1++);
            }
                sheet99.setColumnWidth(0, 75 * 256);
                sheet99.setColumnWidth(1, 35 * 256);
                sheet99.setColumnWidth(2, 35 * 256);
                sheet99.setColumnWidth(3, 35 * 256);
                sheet99.setColumnWidth(4, 20 * 256);
            }

            //附加空白
            contRow.createCell(i++).setCellValue("");
        }

        /** 设置宽度*/
        sheet.setColumnWidth(0, 10 * 256);
        sheet.setColumnWidth(1, 90 * 256);
        sheet.setColumnWidth(2, 20 * 256);
        sheet.setColumnWidth(3, 60 * 256);
        sheet.setColumnWidth(4, 23 * 256);
        sheet.setColumnWidth(5, 23 * 256);
        sheet.setColumnWidth(6, 20 * 256);


        /** 保存*/
        try {
            FileOutputStream fout = new FileOutputStream(paths);
            wb.write(fout);
            fout.close();
            //Logger.getLogger(DownloadUtil.class).info("Excel导出完成：" +paths);
            return true;}
        catch (Exception e) {
            //Logger.getLogger(DownloadUtil.class).error("Excel下载功能导出失败！");
            e.printStackTrace();
            return false;
        }
    }


}
